<?php
/**
 * SKU品质报表
 */
namespace App\Logic\Quality;

use App\Models\Amazon\FbaCustomerReturn;
use App\Models\Basics\SysICountry;
use App\Models\Product\ProductAmazonSku;
use App\Models\Quality\QDetail;
use App\Models\Quality\QReport;
use App\Models\SalesAmazon\SalesVolumeDay;
use Illuminate\Support\Facades\DB;
class Report
{
    public static function init($date)
    {
        try{
            $time = strtotime($date);

            //Amazon产品
            $res = ProductAmazonSku::query()->select('country_id','psku_id')->distinct()->get();
            $i = 0;
            $num = 800;
            $_start = date('Y-m-01',$time);
            $_end = date('Y-m-t',$time);
            $area = [];
            while(true){
                if($res->isEmpty())break;
                $_res = $res->splice($i,$num);
                DB::beginTransaction();
                foreach($_res as $_re){
                    $where = [];
                    $where['report_date'] = $date;
                    $where['country_id'] = $_re->country_id;
                    $where['psku_id'] = $_re->psku_id;
                    $temp = [];
                    if(!array_key_exists($_re->country_id,$area)){
                        $area[$_re->country_id] = SysICountry::getAreaIdByCountryId($_re->country_id);
                    }
                    $temp['area_id'] = $area[$_re->country_id];
                    $temp['sales_num'] = self::_salesNum($time,$_re->country_id,$_re->psku_id);
                    $return = self::_return($time,$_re->country_id,$_re->psku_id);
                    $temp['return_num'] = $return->return_num ?? 0; //return_num：退货总量
                    $temp['saleable_num'] = $return->saleable_num ?? 0; //saleable_num：可二次销售总量
                    $temp['size'] = self::_problem($_start,$_end,$_re->country_id,$_re->psku_id,1);
                    $temp['produce'] = self::_problem($_start,$_end,$_re->country_id,$_re->psku_id,2);
                    $temp['design'] = self::_problem($_start,$_end,$_re->country_id,$_re->psku_id,3);
                    $temp['subject'] = self::_problem($_start,$_end,$_re->country_id,$_re->psku_id,4);
                    QReport::query()->updateOrCreate($where,$temp);
                }
                DB::commit();
            }
        }catch (\Exception $e){
            report($e);
        }
    }

    /**
     * 销量
     * @param $time
     * @param $countryId
     * @param $pSkuId
     * @return int|mixed
     */
    private static function _salesNum($time,$countryId,$pSkuId){
        $_start = date('Ym01',$time);
        $_end = date('Ymt',$time);
        return SalesVolumeDay::query()
            ->whereBetween('day',[$_start,$_end])
            ->where('country_id',$countryId)
            ->where('psku_id',$pSkuId)
            ->sum('volume');
    }

    /**
     * 退货数量（return_num：退货总量，saleable_num：可二次销售总量）
     * @param $time
     * @param $countryId
     * @param $pSkuId
     * @return \Illuminate\Database\Eloquent\Builder|\Illuminate\Database\Eloquent\Model|object|null
     */
    private static function _return($time,$countryId,$pSkuId){
        $start = date('Y-m-01 00:00:00',$time);
        $end = date('Y-m-t 23:59:59',$time);
        return FbaCustomerReturn::query()
            ->where('return_date','>=',$start)
            ->where('return_date','<=',$end)
            ->where('country_id',$countryId)
            ->where('psku_id',$pSkuId)
            ->select(DB::raw("sum(quantity) as return_num,sum(IF(detailed_disposition='SELLABLE',quantity,0)) as saleable_num"))
            ->first();
    }

    /**
     * （标签）问题数量
     * @param $start
     * @param $end
     * @param $countryId
     * @param $pSkuId
     * @param $type
     * @return array
     */
    private static function _problem($start,$end,$countryId,$pSkuId,$type){
        return QDetail::query()
            ->where('report_date','>=',$start)
            ->where('report_date','<=',$end)
            ->where('country_id',$countryId)
            ->where('psku_id',$pSkuId)
            ->where('type',$type)
            ->groupBy('tag')
            ->select('tag',DB::raw('sum(num) as num'))
            ->pluck('num','tag')
            ->toArray();
    }
}
